#coding=utf-8
import MySQLdb,os,sys,csv,MySQLdb.cursors
stdi,stdo,stde=sys.stdin,sys.stdout,sys.stderr
reload(sys)
sys.setdefaultencoding('utf-8')
sys.stdin,sys.stdout,sys.stderr=stdi,stdo,stde

path=os.getcwd()+'/'
origin_file=path+'course.csv'
input_file={'error':path+'error.txt','right':path+'right.txt'}

def print_out(data,after=0):
    if after==0:
        after=''
    else:
        after=str(after);
    temp_str='ID：'+str(data['student_intention_id'])+'--学生ID:'+str(data['student_id'])+'--学生手机：'+str(data['phone'])+'--学生姓名：'+data['name']+'--修改前：'+str(data['remaining_time'])+'--修改后：'+str(after)
    print temp_str
    temp_str+="\r\n"
    write_right_file(temp_str)

def write_error_file(str):
    global input_file
    f=open(input_file['error'],'a',)
    f.write(str)
    f.close()
def write_right_file(str):
    global input_file
    f=open(input_file['right'],'a',)
    f.write(str)
    f.close()
def sendMail(to,html='',attachs={}):
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart
    import smtplib
    import os.path,base64
    #创建一个带附件的实例
    msg = MIMEMultipart()
    #构造正文
    if html.strip()!='':

        body = MIMEText(html,'html','utf-8')
    else:
        body = MIMEText('(空内容)','html','utf-8')
    msg.attach(body)

    #附件
    for attach in attachs:
        temp=attach
        attach=attachs[attach]
        if os.path.isfile(attach):

            #构造附件1
            attach = MIMEText(open(attach, 'rb').read(), 'base64', 'utf-8')
            attach["Content-Type"] = 'application/octet-stream'
            attach["Content-Disposition"] = 'attachment; filename="'+temp+'.txt'
        else:
            attach= MIMEText('','html','utf-8')
        msg.attach(attach)

    #加邮件头信息
    msg['subject'] = html[0:20]
    msg['from'] = base64.decodestring('bHVfeGlhb2ZlaUBzaW5hLmNu')
    #msg['to'] = '1175995117@qq.com'
    msg['to'] = to

    #发送邮件
    try:
        server = smtplib.SMTP()
        server.connect('smtp.sina.com')
        server.login(base64.decodestring('bHVfeGlhb2ZlaUBzaW5hLmNu'),base64.decodestring('UTExMTExMQ=='))
        server.sendmail(msg['from'], msg['to'],msg.as_string())
        server.quit()
        print('发送成功')
    except Exception as e:
        print(str(e))


print '初始化...'
#初始化
for temp_file in input_file:
    temp_file=path+temp_file+'.txt'
    if os.path.isfile(temp_file):
      print temp_file
      os.remove(temp_file)
print '开始执行...'

#打开csv
course_file=open(origin_file, 'rb')

reader=csv.reader(course_file)
rows=[];
for row in reader:
    rows.append(row)
course_file.close()


conn = MySQLdb.connect("54json.com","slave","slave","student",charset='utf8',cursorclass = MySQLdb.cursors.DictCursor)

cursor = conn.cursor()

for row in rows:
    tt=row[0].decode('gbk')
    if tt and row[1]:
       cursor.execute ("select * from student_copy where name='"+tt+"'")
       count=cursor.rowcount
       count=int(count)
       if count==1:
          pass
          data=cursor.fetchone()
          print tt+'--'+str(count)
          if float(row[1])!=0:
              after=0
              try:
                  res=cursor.execute('update student_copy set remaining_time=remaining_time+%s where student_intention_id=%s',[float(row[1]),data['student_intention_id']])
                  if res:
                      conn.commit()
                  print tt+'--修改成功----------------'
                  after=float(data['remaining_time'])+float(row[1])
              except MySQLdb.Error,e:
                  print "Mysql Error %d: %s" % (e.args[0], e.args[1])
              finally:
                  print_out(data,after)
       elif count==2:
          pass
          datas=cursor.fetchall()
          temp_data={}
          temp_remaining_time=0.0
          for index,data in enumerate(datas):
              if index==0:
                  if data['remaining_time']!=0.0:
                      temp_data=data
                  temp_remaining_time=data['remaining_time']
              else:
                  if temp_data:
                      if data['remaining_time']!=0.0:
                         temp_data={}
                  else:
                      if data['remaining_time']!=0.0:
                          temp_data=data
          if temp_data:
              try:
                  res=cursor.execute('update student_copy set remaining_time=remaining_time+%s where student_intention_id=%s',[float(row[1]),temp_data['student_intention_id']])
                  if res:
                      conn.commit()
                  print tt+'--修改成功----------------'
              except MySQLdb.Error,e:
                  print "Mysql Error %d: %s" % (e.args[0], e.args[1])
              finally:
                  print_out(data)
          else:
              temp_str=tt+'--'+str(row[1])+'--此用户信息为2条remaining_time异常的信息'+"\r\n"
              write_error_file(temp_str)
       elif count>=3:
           temp_str=tt+'--'+str(row[1])+'--此用户信息大于2条为'+str(count)+'条数据请人工处理'+"\r\n"
           write_error_file(temp_str)
       else:
           temp_str=tt+'--'+str(row[1])+'--没有匹配到信息'+"\r\n"
           write_error_file(temp_str)
cursor.close ()
conn.close ()
print '执行结束,发送邮件中...'
#发送邮件
sendMail('1175995117@qq.com','job_execute_result',input_file)



